﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
namespace syc.Sale
{
    public partial class HeTong_FuLiao_GongYePin_MaiMai : System.Web.UI.Page
    {
        protected HSSFWorkbook hssfworkbook;
        protected string 供方;
        protected string 需方;
        protected string 合同编号;
        protected string 签订日期;
        protected string 金额大写;
        protected string 供方地址;
        protected string 供方电话;
        protected string 开户银行;
        protected string 帐号;
        protected string 邮编;
        protected string 税号;
        protected string 品名;
        protected string 规格;
        protected string 数量;
        protected string 单位;
        protected string 单价;
        protected string 金额;
        protected string 交货时间;
        protected string 交货数量;

        protected void Page_Load(object sender, EventArgs e)
        {
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            String HeTongHao = Request.QueryString["HeTongHao"];
            String sql = "select 品名,规格,数量,单位,辅料采购合同.单价,金额,交货时间,交货数量 from 材料 inner join 辅料采购合同 on 材料.ID=辅料采购合同.辅料ID where 合同编号='" + Request.QueryString["HeTongHao"] + "'";
            if (!this.IsPostBack)
            {
               
                this.SqlDataSource1.SelectCommand = sql;
                this.ViewState["SelectCommand"] = sql;
            }
            this.SqlDataSource1.SelectCommand = (string)this.ViewState["SelectCommand"];
          
          
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            Cmd.CommandText = "select 详细名称,地址,电话,开户银行,账号,邮政编码,税号 from 辅料供货单位 inner join 辅料采购合同 on 辅料采购合同.供货单位ID=辅料供货单位.ID where 辅料采购合同.合同编号='" + Request.QueryString["HeTongHao"] + "'";
            SqlDataReader read = Cmd.ExecuteReader();
            if (read.Read())
            {
                if (read.IsDBNull(0))
                {
                    this.Label1.Text = "";
                }
                else
                {
                    this.Label1.Text = read.GetString(0);
                }
                if (read.IsDBNull(1))
                {
                    this.Label2.Text = "";
                    供方地址 = "";
                }
                else
                {
                    this.Label2.Text = read.GetString(1);
                    供方地址 = this.Label2.Text;
                }               
                if (read.IsDBNull(2))
                {
                    this.Label5.Text = "";
                    供方电话 = "";
                }
                else
                {
                    this.Label5.Text = read.GetString(2);
                    供方电话 = this.Label5.Text;
                }
                if (read.IsDBNull(3))
                {
                    this.Label6.Text = "";
                    开户银行 = "";
                }
                else
                {
                    this.Label6.Text = read.GetString(3);
                    开户银行 = this.Label6.Text;
                }
                if (read.IsDBNull(4))
                {
                    this.Label7.Text = "";
                    帐号 = "";
                }
                else
                {
                    this.Label7.Text = read.GetString(4);
                    帐号 = this.Label7.Text;
                }
                if (read.IsDBNull(5))
                {
                    this.Label8.Text = "";
                    邮编 = "";
                }
                else
                {
                    this.Label8.Text = read.GetString(5);
                    邮编 = this.Label8.Text;
                }
                if (read.IsDBNull(6))
                {
                    this.Label9.Text = "";
                    税号 = "";
                }
                else
                {
                    this.Label9.Text = read.GetString(6);
                    税号 = this.Label9.Text;
                }
            }
            read.Close();
            Cmd.Dispose();
            Conn.Close();
        }

        public override void VerifyRenderingInServerForm(Control control)
        {

        }

        public void ToWord(System.Web.UI.Control ctl, String filetype, String filename)
        {
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Charset = "";
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=\"" + Server.UrlEncode(filename) + ".doc");

            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            //更改ContentType的值为ms-word即可实现导出到Word  
            HttpContext.Current.Response.ContentType = filetype;//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword  
            ctl.Page.EnableViewState = false;
            System.IO.StringWriter tw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
            ctl.RenderControl(hw);
            HttpContext.Current.Response.Write(tw.ToString());
            HttpContext.Current.Response.End();
        }
        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("/Sale/模板/辅料工业品买卖合同.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "辅料工业品买卖合同";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "辅料工业品买卖合同";
            hssfworkbook.SummaryInformation = si;
        }
        protected void WriteToFile()
        {
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + DateTime.Today.ToShortDateString() + ".xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }


        Decimal sum = 0;
        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {

            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                DataRowView myrows = (DataRowView)e.Row.DataItem;
                sum += Convert.ToDecimal(myrows[5].ToString());
            }
            syc.Dal.Sale.DecimalToUpper tomoney = new syc.Dal.Sale.DecimalToUpper();
            String money = tomoney.ToUpper(sum);
            if (e.Row.RowType == DataControlRowType.Footer)
            {

                e.Row.Cells[0].Text = "合计人民币(大写)：" + money;
                金额大写 = money;
                e.Row.Cells[0].ColumnSpan = 9;
                e.Row.Cells[1].Visible = false;
                e.Row.Cells[2].Visible = false;
                e.Row.Cells[3].Visible = false;
                e.Row.Cells[4].Visible = false;
                e.Row.Cells[5].Visible = false;
                e.Row.Cells[6].Visible = false;
                e.Row.Cells[7].Visible = false;
                // e.Row.Cells[8].Visible = false;
            }

        }        

        protected void Button1_Click(object sender, EventArgs e)
        {
            //this.Button.Visible = false;           
            //ToWord(this.Table1,"application/ms-word ", "hetong");
            供方 = Request.QueryString["GongFang"];
            合同编号 = Request.QueryString["HeTongHao"].Trim();
            签订日期 = DateTime.Parse(Request.QueryString["Date"]).ToString("yyyy年MM月dd日");
            InitializeWorkbook();
            HSSFSheet sheet1 = hssfworkbook.GetSheet("辅料合同");
            sheet1.GetRow(1).GetCell(1).SetCellValue("供方：" + 供方);
            sheet1.GetRow(1).GetCell(7).SetCellValue("合同编号：" + 合同编号);
            sheet1.GetRow(3).GetCell(7).SetCellValue("签订时间：" + 签订日期);
            this.GridView1.DataBind();
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            String sql = "select 品名,规格,数量,单位,辅料采购合同.单价,金额,交货时间,交货数量 from 材料 inner join 辅料采购合同 on 材料.ID=辅料采购合同.辅料ID where 合同编号='" + Request.QueryString["HeTongHao"] + "'";
            DataSet ds = new DataSet();
            SqlDataAdapter sda = new SqlDataAdapter(sql, Conn);
            sda.Fill(ds);
            品名 = ds.Tables[0].Rows[0][0].ToString();
            规格 = ds.Tables[0].Rows[0][1].ToString();
            数量 = ds.Tables[0].Rows[0][2].ToString();
            单位 = ds.Tables[0].Rows[0][3].ToString();
            单价 = ds.Tables[0].Rows[0][4].ToString();
            金额 = ds.Tables[0].Rows[0][5].ToString();
            交货时间 = ds.Tables[0].Rows[0][6].ToString();
            交货数量 = ds.Tables[0].Rows[0][7].ToString();
            sheet1.GetRow(9).GetCell(0).SetCellValue(品名);
            sheet1.GetRow(9).GetCell(1).SetCellValue(规格);
            sheet1.GetRow(9).GetCell(2).SetCellValue(数量);
            sheet1.GetRow(9).GetCell(3).SetCellValue(单位);
            sheet1.GetRow(9).GetCell(4).SetCellValue(单价);
            sheet1.GetRow(9).GetCell(5).SetCellValue(金额);
            sheet1.GetRow(9).GetCell(6).SetCellValue(交货时间);          
            sheet1.GetRow(9).GetCell(8).SetCellValue(交货数量);
            sheet1.GetRow(10).GetCell(2).SetCellValue(金额大写);
            sheet1.GetRow(28).GetCell(1).SetCellValue(供方地址);
            sheet1.GetRow(31).GetCell(0).SetCellValue("电话(传真)：" + 供方电话);
            sheet1.GetRow(33).GetCell(0).SetCellValue(开户银行);
            sheet1.GetRow(34).GetCell(0).SetCellValue("账号：" + 帐号);
            sheet1.GetRow(34).GetCell(2).SetCellValue("72060154700000459");
            sheet1.GetRow(35).GetCell(0).SetCellValue("邮政编码：" + 邮编);
            sheet1.GetRow(36).GetCell(0).SetCellValue("税号：" + 税号);
            //sheet1.GetRow(4).GetCell(1).SetCellValue(ntaojihua);
            //sheet1.GetRow(4).GetCell(2).SetCellValue((ytao / 1000).ToString("0.000"));
            //sheet1.GetRow(4).GetCell(3).SetCellValue(ntao.ToString("0.000"));
            //sheet1.GetRow(4).GetCell(4).SetCellValue(qntao.ToString("0.000"));
            //sheet1.GetRow(4).GetCell(5).SetCellValue("");
            //sheet1.GetRow(4).GetCell(6).SetCellValue(ytcz.ToString("0.00"));
            //sheet1.GetRow(4).GetCell(7).SetCellValue(tljcz.ToString("0.00"));
            //sheet1.GetRow(4).GetCell(8).SetCellValue(qntljcz.ToString("0.00"));

            //sheet1.GetRow(5).GetCell(1).SetCellValue(nyoujihua);
            //sheet1.GetRow(5).GetCell(2).SetCellValue((yyou / 1000).ToString("0.000"));
            //sheet1.GetRow(5).GetCell(3).SetCellValue(nyou.ToString("0.000"));
            //sheet1.GetRow(5).GetCell(4).SetCellValue(qnyou.ToString("0.000"));
            //sheet1.GetRow(5).GetCell(5).SetCellValue("");
            //sheet1.GetRow(5).GetCell(6).SetCellValue(yycz.ToString("0.00"));
            //sheet1.GetRow(5).GetCell(7).SetCellValue(yljcz.ToString("0.00"));
            //sheet1.GetRow(5).GetCell(8).SetCellValue(qnyljcz.ToString("0.00"));

            WriteToFile();
            Response.End();

        }
      
    }
}
